﻿/*
	dbo.SpGetDefaultParameterForUser Procedure created by Jens Suessmeyer (12/09/2009)

	Procedure to get the last recently used parameter values in a report for a certain user
	and a specific server and a specific report
	
	Input Parameters:	@UserId		= UserId passed in by the Reportexecution call (should be hashed for security reasons
						@ReportServerUrl	= ReportServer Url where the report was executed
						@ReportName	= Report Name which called the proceedure
						@ParameterName	= Parameter Name to get the values for
						@DataType	= Data Type to be returned to Reporting Services (Parameter are passed in by object from RS)
	Output			:	Table containing one column (ParameterValue)
*/


CREATE PROCEDURE [dbo].[SpGetDefaultParameterForUser]
@UserId VARCHAR (50), @ReportServerUrl VARCHAR (128), @ReportName VARCHAR (128), @ParameterName VARCHAR (128), @DataType [sysname]
AS
IF (SELECT TOP 1 ConfigValue FROM dbo.Configuration WHERE ConfigType = 'HashUserId') = 1
	SET @UserId = HashBytes('SHA1', @UserId)
	
DECLARE @ParameterValue VARCHAR(MAX)
SET @ParameterValue = ''

DECLARE @ROWCOUNT INT

DECLARE @LatestExecutionId INT

--Start :>>>>>>thankful update from Codeplex -- User: "blase"
		--avoids reports from initial failing when user runs report the FIRST time
		--Check if the user already has default parameters, if not return NULL
		SELECT TOP(1) @LatestExecutionId = ExecutionId
		FROM        dbo.DefaultReportParameterExecution Pe
		WHERE        Pe.UserId = @UserId 
		ORDER BY    ExecutionTime DESC

		IF (@LatestExecutionId IS NULL) 
			BEGIN
			SELECT NULL AS ParameterValue
			RETURN
			END
		-- end Check
--End :<<<<<< Update from Codeplex 

--Get the latest execution of the report for the particular user
SELECT TOP(1) @LatestExecutionId = ExecutionId
FROM dbo.DefaultReportParameterExecution Pe
WHERE
	Pe.UserId = @UserId 
	AND Pe.ReportName = @ReportName
	AND Pe.ReportServerUrl = @ReportServerUrl
ORDER BY ExecutionTime DESC

--Check first if the information should be tracked at all
IF NOT (SELECT TOP 1 ConfigValue FROM dbo.Configuration WHERE ConfigType = 'TrackParameterValues') = 1
	RETURN 
	
--Get the values the user selected and transform them to be eventually
--multi values to be selected
SELECT @ParameterValue = @ParameterValue + ParameterValue + ';'
FROM dbo.DefaultReportParameters P
WHERE
	P.ParameterName = @ParameterName
	AND P.ExecutionId = @LatestExecutionId

SET @ROWCOUNT = @@ROWCOUNT

PRINT @LatestExecutionId
PRINT @ROWCOUNT 
PRINT LEN(@ParameterValue)
PRINT CASE WHEN @ParameterValue IS NULL THEN 'NULL' ELSE @ParameterValue END

IF @ROWCOUNT = 1 AND @ParameterValue IS NULL
BEGIN
	SELECT NULL AS ParameterValue
	RETURN
END

--Return the values in a typed format
IF @DataType = 'Boolean'
	SELECT CAST(SplitValue AS BIT) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

IF @DataType = 'DateTime'
	SELECT CAST(SplitValue AS DATETIME) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

IF @DataType = 'Text'
	SELECT CAST(SplitValue AS VARCHAR(MAX)) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')
	
IF @DataType = 'Integer'
	SELECT CAST(SplitValue AS INT) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

IF @DataType = 'Float'
	SELECT CAST(SplitValue AS FLOAT) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

RETURN


